﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Metadata.Edm;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;
using NTQOnlineExam.Services.SearchCriteria;

namespace NTQOnlineExam.Services.ReportService.Section
{
  public class GetSectionDTOByIdDbCommand : ICommand<SectionDTO>
  {
    private readonly int _sectionId;

    private const string SQL = @"              
    SELECT s.SectionId, s.SectionName, s.Description, t.TermId, t.TermName
    FROM dbo.Section s INNER JOIN dbo.Term t ON s.TermId = t.TermId
  WHERE s.SectionId = @SectionId
        ";

    public GetSectionDTOByIdDbCommand(int sectionId)
    {
      _sectionId = sectionId;
    }

    public SectionDTO Execute()
    {
      var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
        SectionDTO item = null;

        SqlCommand cmd = new SqlCommand(SQL, connection);
        cmd.Parameters.Add("@SectionId", SqlDbType.Int).Value = _sectionId;
        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        try
        {
          while (reader.Read())
          {
            item = GetDto(reader);
          }
        }
        finally
        {
          reader.Close();
        }
        return item;
      }
    }

    private SectionDTO GetDto(SqlDataReader reader)
    {
      return new SectionDTO
      {
        SectionId = (int) reader["SectionId"],
        SectionName = reader["SectionName"].ToString(),
        Description = reader["Description"] != DBNull.Value ? reader["Description"].ToString() : string.Empty,
        TermId = (int) reader["TermId"],
        TermName = reader["TermName"] != DBNull.Value ? reader["TermName"].ToString() : string.Empty
      };
    }
  }
}
